package sample.compare;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

import java.io.File;
import java.io.FileInputStream;

/**
 * Created by CXinZhi on 2016/10/20.
 */
public class CompareExcel {

	private boolean isMatch;


	private StringBuilder matchMeg;

	public CompareExcel() {
		isMatch = false;
		matchMeg = new StringBuilder();
	}

	public void compareFile(String path1, String path2) {

		try {

			if (!path1.contains(".xls"))
				return;

			// 加载两对比文件
			FileInputStream excellFile1 = new FileInputStream(new File(path1));
			FileInputStream excellFile2 = new FileInputStream(new File(path2));

			// 初始化EXcel 文件
			HSSFWorkbook workbook1 = new HSSFWorkbook(excellFile1);
			HSSFWorkbook workbook2 = new HSSFWorkbook(excellFile2);

			// 获得第一个 EXCEL 的 sheet 表 文件
			HSSFSheet sheet1 = workbook1.getSheetAt(0);
			HSSFSheet sheet2 = workbook2.getSheetAt(0);

			// 开始匹配
			if (compareTwoSheets(sheet1, sheet2)) {
				matchMeg.append(String.format("%s sheet1匹配相等\n", "			"));
				isMatch = true;
				System.out.println(matchMeg.toString());

			} else {
				matchMeg.append(String.format("%s sheet1匹配不相等 退出匹配\n", "	"));
				isMatch = false;
				System.out.println(matchMeg.toString());

			}

			excellFile1.close();
			excellFile2.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}


	// Compare Two Sheets
	public boolean compareTwoSheets(HSSFSheet sheet1, HSSFSheet sheet2) {
		int firstRow1 = sheet1.getFirstRowNum();
		int lastRow1 = sheet1.getLastRowNum();
		boolean equalSheets = true;
		for (int i = firstRow1; i <= lastRow1; i++) {
			//	System.out.println("\n\nComparing Row "+i);
			HSSFRow row1 = sheet1.getRow(i);
			HSSFRow row2 = findRow(row1, i, sheet2);

			if (row1 == null)
				matchMeg.append(String.format("%s 行[%s] 列[%s] 内容是：【%s】在 sheet2 中找不到该 key", "sheet1", (i + 1),
						1, "不存在内容"));
			if (!compareTwoRows(row1, row2, i)) {
				equalSheets = false;
				break;
			}
		}
		return equalSheets;
	}

	private HSSFRow findRow(HSSFRow row1, int rowNum, HSSFSheet sheet2) {
		if (row1 != null) {
			int firstCell1 = row1.getFirstCellNum();
			HSSFCell cell1 = row1.getCell(firstCell1);
			if (getCellValue(cell1).length() < 1)
				matchMeg.append(String.format("%s 行[%s] 列[%s] 内容分别是：【%s】,【%s】", "sheet1", (rowNum + 1),
						1, "不存在内容"));

			int firstRow1 = sheet2.getFirstRowNum();
			int lastRow1 = sheet2.getLastRowNum();
			for (int i = firstRow1; i <= lastRow1; i++) {
				HSSFCell cell2 = sheet2.getRow(i).getCell(firstCell1);

				if (cell2 == null)
					continue;

				if (getCellValue(cell1).equals(getCellValue(cell2)))
					return sheet2.getRow(i);
			}
		}

		return null;
	}

	// Compare Two Rows
	public boolean compareTwoRows(HSSFRow row1, HSSFRow row2, int rowNum) {
		if ((row1 == null) && (row2 == null)) {
			return true;
		} else if ((row1 == null) || (row2 == null)) {
			return false;
		}

		int firstCell1 = row1.getFirstCellNum();
		int lastCell1 = row1.getLastCellNum();
		boolean equalRows = true;

		for (int i = firstCell1; i <= lastCell1; i++) {
			HSSFCell cell1 = row1.getCell(i);
			HSSFCell cell2 = row2.getCell(i);
			if (!compareTwoCells(cell1, cell2)) {
				equalRows = false;
				matchMeg.append(String.format("%s 行[%s] 列[%s] 内容分别是：【%s】,【%s】", "			", (rowNum + 1),
						(i + 1), getCellValue(cell1), getCellValue(cell2)));
				break;
			}
		}
		return equalRows;
	}

	// 对比两 cell 值
	public boolean compareTwoCells(HSSFCell cell1, HSSFCell cell2) {
		if ((cell1 == null) && (cell2 == null)) {
			return true;
		} else if ((cell1 == null) || (cell2 == null)) {
			return false;
		}

		return getCellValue(cell1).equals(getCellValue(cell2));
	}

	private String getCellValue(Cell cell) {
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_FORMULA:
				return cell.getCellFormula();
			case HSSFCell.CELL_TYPE_NUMERIC:
				return String.valueOf(cell.getNumericCellValue());
			case HSSFCell.CELL_TYPE_STRING:
				return String.valueOf(cell.getStringCellValue());
			case HSSFCell.CELL_TYPE_BLANK:
				return String.valueOf(cell.getCellType());
			case HSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf(cell.getBooleanCellValue());
			case HSSFCell.CELL_TYPE_ERROR:
				return String.valueOf(cell.getErrorCellValue());
			default:
				return String.valueOf(cell.getStringCellValue());
		}
	}

	public boolean isMatch() {
		return isMatch;
	}

	public String getMatch() {
		if (isMatch)
			return "1";
		else
			return "0";
	}

	public void setMatch(boolean match) {
		isMatch = match;
	}

	public StringBuilder getMatchMeg() {
		return matchMeg;
	}

	public void setMatchMeg(StringBuilder matchMeg) {
		this.matchMeg = matchMeg;
	}


//	// Compare Two Cells
//	public boolean compareTwoCells(HSSFCell cell1, HSSFCell cell2) {
//		if ((cell1 == null) && (cell2 == null)) {
//			return true;
//		} else if ((cell1 == null) || (cell2 == null)) {
//			return false;
//		}
//
//		boolean equalCells = false;
//		int type1 = cell1.getCellType();
//		int type2 = cell2.getCellType();
//		if (type1 == type2) {
//			if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
//				// Compare cells based on its type
//				switch (cell1.getCellType()) {
//					case HSSFCell.CELL_TYPE_FORMULA:
//						if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
//							equalCells = true;
//						}
//						break;
//					case HSSFCell.CELL_TYPE_NUMERIC:
//						if (cell1.getNumericCellValue() == cell2
//								.getNumericCellValue()) {
//							equalCells = true;
//						}
//						break;
//					case HSSFCell.CELL_TYPE_STRING:
//						if (cell1.getStringCellValue().equals(cell2
//								.getStringCellValue())) {
//							equalCells = true;
//						}
//						break;
//					case HSSFCell.CELL_TYPE_BLANK:
//						if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
//							equalCells = true;
//						}
//						break;
//					case HSSFCell.CELL_TYPE_BOOLEAN:
//						if (cell1.getBooleanCellValue() == cell2
//								.getBooleanCellValue()) {
//							equalCells = true;
//						}
//						break;
//					case HSSFCell.CELL_TYPE_ERROR:
//						if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
//							equalCells = true;
//						}
//						break;
//					default:
//						if (cell1.getStringCellValue().equals(
//								cell2.getStringCellValue())) {
//							equalCells = true;
//						}
//						break;
//				}
//			} else {
//				return false;
//			}
//		} else {
//			return false;
//		}
//		return equalCells;
//	}
}
